﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class WebUserControl_ControlBaoCaoKhuyenMai : System.Web.UI.UserControl
{
    string str = BCDataCenter.Configuration.CurrentConnectionString;
    SqlConnection con;
    SqlCommand drop,command, insert, update1, update2, update3, update4, update5;
    DataSet ds;
    SqlDataAdapter da;
    string sqlDrop,sqlCreate = "";
    string sqlInsert = "", sqlUpdate1 = "", sqlUpdate2 = "", sqlUpdate3 = "", sqlUpdate4 = "", sqlUpdate5="";
    SqlTransaction tran;
    MethodClassCommon common;
    protected void Page_Load(object sender, EventArgs e)
    {
      
    }
   
    void CreateTable()
    {
        //lay adncode
        string a_AdnCode = "";
        common = new MethodClassCommon();
        string usertype = common.GetDataDepend("GET_USERTYPE", "UserName", Session["username"].ToString());
        if (usertype == "ad")
        {
            a_AdnCode = common.GetDataDepend("GET_ANDCODE_ADMIN", "parameter", "BC");
        }
        else if (usertype == "sa")
        {
            a_AdnCode = common.GetDataDepend("GET_ANDCODE_ADMIN", "parameter", "BC.BC" + Session["Sitecode"].ToString());
        }
        else if (usertype == "us")
        {
            a_AdnCode = common.GetDataDepend("GET_ANDCODE_ADMIN", "parameter", Session["username"].ToString());
        }
        //lay tham so ngay
        DateTime dateTest;
        DateTime dateTest1;
        System.Globalization.DateTimeFormatInfo sFormat = new System.Globalization.DateTimeFormatInfo();
        sFormat.ShortDatePattern = "dd/MM/yyyy";
        dateTest = Convert.ToDateTime(txtFromDate.Text, sFormat);
        dateTest1 = Convert.ToDateTime(txtToDate.Text, sFormat);
        string frDate = String.Format("{0:yyyy/MM/dd}", dateTest);
        string toDate = String.Format("{0:yyyy/MM/dd}", dateTest1);

        int FromMonth = Convert.ToInt32(txtFromDate.Text.Substring(3, 2));
        int FromYear = Convert.ToInt32(txtFromDate.Text.Substring(6, 4));
        int ToMonth = Convert.ToInt32(txtToDate.Text.Substring(3, 2));
        int ToYear = Convert.ToInt32(txtToDate.Text.Substring(6, 4));
        double max = ToYear * 12 + ToMonth;
        double i = FromYear * 12 + FromMonth;
        if (max < i)
        {
            System.Windows.Forms.MessageBox.Show("Ngày bắt đầu lấy báo cáo phải nhỏ hơn ngày đích");
        }
        else
        {

            con = new SqlConnection(str);
            con.Open();
            tran = con.BeginTransaction();
        
            try
            {
             //kiểm tra và xóa table
                drop = new SqlCommand();
                drop.Connection = con;
                drop.CommandText = "DELETE_TABLE";
                drop.CommandType = CommandType.StoredProcedure;
                drop.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                drop.Transaction = tran;
                drop.ExecuteNonQuery();

              //tạo table  
                command = new SqlCommand();
                command.Connection = con;
                command.CommandText = "CREATE_TABLE";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                command.Transaction = tran;
                command.ExecuteNonQuery();
              //insert data vào table
                while (i <= max)
                {   
                    insert = new SqlCommand();
                    insert.Connection = con;
                    insert.CommandText = "InsertTableKhuyenMai";
                    insert.CommandType = CommandType.StoredProcedure;
                    insert.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                     if (FromMonth < 10)
                    {
                        insert.Parameters.Add(new SqlParameter("@month","0" + Convert.ToString(FromMonth) + Convert.ToString(FromYear)));
                    }
                    else
                    {
                        insert.Parameters.Add(new SqlParameter("@month",Convert.ToString(FromMonth) + Convert.ToString(FromYear)));
                    }
                    insert.Parameters.Add(new SqlParameter("@fromMonth", frDate));
                    insert.Parameters.Add(new SqlParameter("@toMonth", toDate));
                    insert.Parameters.Add(new SqlParameter("@AdnCode", a_AdnCode));
                    insert.Transaction = tran;
                    try
                    {
                        insert.ExecuteNonQuery();
                    }
                    catch
                    {

                    }
                        FromMonth = FromMonth + 1;
                    if (FromMonth > 12)
                    {
                        FromMonth = 1;
                        FromYear = FromYear + 1;
                    }
                    i = FromYear * 12 + FromMonth;
                }

                update1 = new SqlCommand();
                update1.Connection = con;
                update1.CommandText = "UPDATE_STEP2";
                update1.CommandType = CommandType.StoredProcedure;
                update1.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                update1.Transaction = tran;
                update1.ExecuteNonQuery();

                update2 = new SqlCommand();
                update2.Transaction = tran;
                update2.Connection = con;
                update2.CommandText = "UPDATE_STEP3";
                update2.CommandType = CommandType.StoredProcedure;
                update2.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                update2.ExecuteNonQuery();

                update3 = new SqlCommand();
                update3.Transaction = tran;
                update3.Connection = con;
                update3.CommandText = "UPDATE_STEP4";
                update3.CommandType = CommandType.StoredProcedure;
                update3.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                update3.ExecuteNonQuery();

                update4 = new SqlCommand();
                update4.Transaction = tran;
                update4.Connection = con;
                update4.CommandText = "UPDATE_STEP5";
                update4.CommandType = CommandType.StoredProcedure;
                update4.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                update4.ExecuteNonQuery();

                update5 = new SqlCommand();
                update5.Transaction = tran;
                update5.Connection = con;
                update5.CommandText = "UPDATE_STEP6";
                update5.CommandType = CommandType.StoredProcedure;
                update5.Parameters.Add(new SqlParameter("@TableName", Session["Sitecode"].ToString() + "_CustOffTake"));
                update5.ExecuteNonQuery();

                
                tran.Commit();
            }
            catch(Exception e)
            {
                tran.Rollback();
                System.Windows.Forms.MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }

   
    protected void Button1_Click(object sender, EventArgs e)
    {
        CreateTable();
        SqlDataSource2.ConnectionString = str;
        SqlDataSource2.SelectParameters["TableName"].DefaultValue = Session["Sitecode"].ToString() + "_CustOffTake";
        ASPxPivotGrid1.DataSourceID = "SqlDataSource2";
        try
        {
            ASPxPivotGrid1.DataBind();
        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show("Không có dữ liệu");
        }
    }
}
